﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using eShop.DataAccess.Entity;

namespace eShop.DataAccess
{
    public partial class UserDAL : BaseDAL
    {
        public static bool Insert(User user)
        {
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("User_InsertOrUpdate");
            db.AddOutParameter(dbCommand, "Id", DbType.Int32, 4);
            db.AddInParameter(dbCommand, "UserName", DbType.String, user.UserName);
            db.AddInParameter(dbCommand, "Password", DbType.String, user.Password);
            db.AddInParameter(dbCommand, "Role", DbType.String, user.Role);
            if (user.CreatedOn.HasValue)
                db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, user.CreatedOn);
            else
                db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, DBNull.Value);

            if (db.ExecuteNonQuery(dbCommand) > 0)
            {
                user.Id = Convert.ToInt32(db.GetParameterValue(dbCommand, "@Id"));
                return true;
            }
            return false;
        }

        public static bool Update(User user)
        {
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("User_InsertOrUpdate");
            db.AddInParameter(dbCommand, "Id", DbType.Int32, user.Id);
            db.AddInParameter(dbCommand, "UserName", DbType.String, user.UserName);
            db.AddInParameter(dbCommand, "Password", DbType.String, user.Password);
            db.AddInParameter(dbCommand, "Role", DbType.String, user.Role);
            if (user.CreatedOn.HasValue)
                db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, user.CreatedOn);
            else
                db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, DBNull.Value);

            if (db.ExecuteNonQuery(dbCommand) > 0)
                return true;
            return false;
        }

        public static List<User> GetAll(Boolean allowPaging = false, Int32 pageIndex = 0, Int32 pageSize = 0, String orderColumn = "Id", Boolean isAscending = false)
        {
            List<User> userList = new List<User>();
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("User_GetAll");
            db.AddInParameter(dbCommand, "AllowPaging", DbType.Boolean, allowPaging);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, pageIndex);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, pageSize);
            db.AddInParameter(dbCommand, "OrderColumn", DbType.String, orderColumn);
            db.AddInParameter(dbCommand, "IsAscending", DbType.Boolean, isAscending);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                userList = GetEntitiesFromReader(dataReader);
            }
            return userList;
        }

        public static List<User> GetByKeys(Int32? id)
        {
            List<User> userList = new List<User>();
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("User_GetByKeys");
            if (id.HasValue)
                db.AddInParameter(dbCommand, "Id", DbType.Int32, id);

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                userList = GetEntitiesFromReader(dataReader);
            }
            return userList;
        }

        public static bool DeleteByKeys(Int32? id)
        {
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("User_DeleteByKeys");
            if (id.HasValue)
                db.AddInParameter(dbCommand, "Id", DbType.Int32, id);
            if (db.ExecuteNonQuery(dbCommand) > 0)
                return true;
            return false;
        }

        private static List<User> GetEntitiesFromReader(IDataReader dataReader)
        {
            List<User> userList = new List<User>();
            while (dataReader.Read())
            {
                userList.Add(GetEntityFromReader(dataReader));
            }
            return userList;
        }

        private static User GetEntityFromReader(IDataReader dataReader)
        {
            User user = new User();
            user.Id = (Int32)dataReader["Id"];
            user.UserName = (String)dataReader["UserName"];
            user.Password = (String)dataReader["Password"];
            user.Role = (String)dataReader["Role"];
            if (dataReader["CreatedOn"] == System.DBNull.Value)
                user.CreatedOn = null;
            else
                user.CreatedOn = (DateTime)dataReader["CreatedOn"];

            if (dataReader["IsActive"] == System.DBNull.Value)
                user.IsActive = false;
            else
                user.IsActive = (Boolean)dataReader["IsActive"];
                        
            return user;
        }

        public static List<User> GetUsersPaged(string userName, string orderBy, bool isAscending, int pageIndex, int pageSize)
        {
            List<User> userList = new List<User>();
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("User_GetUsersPaged");
            db.AddInParameter(dbCommand, "UserName", DbType.String, userName);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, pageIndex);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, pageSize);
            db.AddInParameter(dbCommand, "OrderColumn", DbType.String, orderBy);
            db.AddInParameter(dbCommand, "IsAscending", DbType.Boolean, isAscending);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                userList = GetEntitiesFromReader(dataReader);
            } 
            
            return userList;
        }

        public static int GetUsersPagedCount(string userName)
        {
            Int32 totalCount = 0;
            Database db = GetDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("User_GetUsersPagedCount");
            db.AddParameter(dbCommand, "ReturnVal", DbType.Int32, ParameterDirection.ReturnValue, "totalCount", DataRowVersion.Default, null);
            db.AddInParameter(dbCommand, "UserName", DbType.String, userName);
            if (db.ExecuteNonQuery(dbCommand) > 0)
            {
                totalCount = Convert.ToInt32(db.GetParameterValue(dbCommand, "@ReturnVal"));

            }

            return totalCount;
           
        }
    }
}

